/**
 * MySQL to MongoDB Migration Script
 * 将 MySQL 中的 tracking 数据迁移到 MongoDB
 */

const mysql = require('mysql2/promise');
const { MongoClient } = require('mongodb');
require('dotenv').config({ path: '.env.local' });

// 数据库配置
const mysqlConfig = {
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  user: process.env.DB_USER,
  password: process.env.DB_PWD,
  database: process.env.DB_DATABASE
};

const mongoConfig = {
  host: process.env.MONGO_HOST,
  port: process.env.MONGO_PORT,
  database: process.env.MONGO_DATABASE,
  username: process.env.MONGO_USERNAME,
  password: process.env.MONGO_PASSWORD
};

async function migrateData() {
  let mysqlConnection;
  let mongoClient;

  try {
    console.log('🚀 开始数据迁移...');

    // 连接 MySQL
    console.log('📡 连接 MySQL...');
    mysqlConnection = await mysql.createConnection(mysqlConfig);
    console.log('✅ MySQL 连接成功');

    // 连接 MongoDB
    console.log('📡 连接 MongoDB...');
    const mongoUri = `mongodb://${mongoConfig.username}:${encodeURIComponent(mongoConfig.password)}@${mongoConfig.host}:${mongoConfig.port}/${mongoConfig.database}?authSource=${mongoConfig.database}`;
    mongoClient = new MongoClient(mongoUri);
    await mongoClient.connect();
    const mongoDB = mongoClient.db(mongoConfig.database);
    console.log('✅ MongoDB 连接成功');

    // 迁移网站数据
    console.log('📊 迁移网站数据...');
    const [websites] = await mysqlConnection.execute('SELECT * FROM websites ORDER BY id');
    console.log(`找到 ${websites.length} 个网站记录`);

    if (websites.length > 0) {
      // 清空现有数据
      await mongoDB.collection('websites').deleteMany({});
      
      // 创建 ID 映射表
      const websiteIdMap = new Map();
      
      const websiteDocs = websites.map(website => {
        const mongoDoc = {
          domain: website.domain,
          name: website.name,
          description: website.description,
          isActive: Boolean(website.isActive),
          trackingId: website.trackingId,
          ignoreLocalhost: Boolean(website.ignoreLocalhost),
          collectIp: Boolean(website.collectIp),
          collectUserAgent: Boolean(website.collectUserAgent),
          collectReferrer: Boolean(website.collectReferrer),
          createdAt: website.createdAt,
          updatedAt: website.updatedAt
        };
        return mongoDoc;
      });

      const websiteResult = await mongoDB.collection('websites').insertMany(websiteDocs);
      console.log(`✅ 成功迁移 ${websiteResult.insertedCount} 个网站记录`);

      // 建立 ID 映射关系
      const insertedWebsites = await mongoDB.collection('websites').find({}).toArray();
      websites.forEach((mysqlWebsite, index) => {
        const mongoWebsite = insertedWebsites.find(w => w.trackingId === mysqlWebsite.trackingId);
        if (mongoWebsite) {
          websiteIdMap.set(mysqlWebsite.id, mongoWebsite._id);
        }
      });

      // 迁移页面访问数据
      console.log('📊 迁移页面访问数据...');
      const [pageViews] = await mysqlConnection.execute('SELECT * FROM page_views ORDER BY id');
      console.log(`找到 ${pageViews.length} 个页面访问记录`);

      if (pageViews.length > 0) {
        // 清空现有数据
        await mongoDB.collection('page_views').deleteMany({});
        
        // 分批处理，避免内存溢出
        const batchSize = 1000;
        let processedCount = 0;

        for (let i = 0; i < pageViews.length; i += batchSize) {
          const batch = pageViews.slice(i, i + batchSize);
          const pageViewDocs = batch
            .filter(pv => websiteIdMap.has(pv.websiteId)) // 只迁移有对应网站的记录
            .map(pageView => ({
              websiteId: websiteIdMap.get(pageView.websiteId),
              url: pageView.url,
              title: pageView.title,
              referrer: pageView.referrer,
              userAgent: pageView.userAgent,
              ip: pageView.ip,
              country: pageView.country,
              city: pageView.city,
              browser: pageView.browser,
              os: pageView.os,
              device: pageView.device,
              screenSize: pageView.screenSize,
              language: pageView.language,
              customData: pageView.customData ? JSON.parse(pageView.customData) : {},
              timeOnPage: pageView.timeOnPage || 0,
              isUnique: Boolean(pageView.isUnique),
              sessionId: pageView.sessionId,
              createdAt: pageView.createdAt,
              updatedAt: pageView.createdAt // MongoDB 会自动管理 updatedAt
            }));

          if (pageViewDocs.length > 0) {
            await mongoDB.collection('page_views').insertMany(pageViewDocs);
            processedCount += pageViewDocs.length;
            console.log(`已处理 ${processedCount}/${pageViews.length} 个页面访问记录`);
          }
        }

        console.log(`✅ 成功迁移 ${processedCount} 个页面访问记录`);
      }
    }

    // 创建索引
    console.log('📊 创建 MongoDB 索引...');
    
    // 网站索引
    await mongoDB.collection('websites').createIndex({ domain: 1 }, { unique: true });
    await mongoDB.collection('websites').createIndex({ trackingId: 1 });
    await mongoDB.collection('websites').createIndex({ isActive: 1 });
    
    // 页面访问索引
    await mongoDB.collection('page_views').createIndex({ websiteId: 1, createdAt: -1 });
    await mongoDB.collection('page_views').createIndex({ url: 1, createdAt: -1 });
    await mongoDB.collection('page_views').createIndex({ ip: 1, createdAt: -1 });
    await mongoDB.collection('page_views').createIndex({ sessionId: 1 });
    await mongoDB.collection('page_views').createIndex({ isUnique: 1 });
    await mongoDB.collection('page_views').createIndex({ createdAt: -1 });
    
    console.log('✅ 索引创建完成');

    // 验证迁移结果
    console.log('🔍 验证迁移结果...');
    const mongoWebsiteCount = await mongoDB.collection('websites').countDocuments();
    const mongoPageViewCount = await mongoDB.collection('page_views').countDocuments();
    
    console.log(`MySQL 网站数量: ${websites.length}`);
    console.log(`MongoDB 网站数量: ${mongoWebsiteCount}`);
    console.log(`MySQL 页面访问数量: ${pageViews ? pageViews.length : 0}`);
    console.log(`MongoDB 页面访问数量: ${mongoPageViewCount}`);

    console.log('🎉 数据迁移完成！');

  } catch (error) {
    console.error('❌ 迁移失败:', error);
    throw error;
  } finally {
    // 关闭连接
    if (mysqlConnection) {
      await mysqlConnection.end();
      console.log('📡 MySQL 连接已关闭');
    }
    if (mongoClient) {
      await mongoClient.close();
      console.log('📡 MongoDB 连接已关闭');
    }
  }
}

// 运行迁移
if (require.main === module) {
  migrateData()
    .then(() => {
      console.log('✅ 迁移脚本执行完成');
      process.exit(0);
    })
    .catch((error) => {
      console.error('❌ 迁移脚本执行失败:', error);
      process.exit(1);
    });
}

module.exports = { migrateData };
